import pymysql
import codecs
import json
import utils
import re


# 违规数据表格 数据入mysql库
def json_insert_Violation_data_table():
    results = []
    f = open("Violation_data_table.json", "r", encoding="utf-8")
    for line in f.readlines():
        results.append(json.loads(line))
    mysql_insert_violation_data_table_mysql(results)

# 违规表格 数据入库
def mysql_insert_violation_data_table_mysql(results):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='',  # 数据库名
        charset='utf8mb4'  # 连接编码
    )
    cursor = conn.cursor()
    i = 0
    for result in results:
        sql_select_mysql = """SELECT * FROM update_time_violation_info 
                                WHERE 时间='{0}' AND 原始ID = '{1}' AND 违规内容 = '{2}'""".format(
            result["时间"], result["原始ID"], result["违规内容"])
        # 执行语句
        effect_row = cursor.execute(sql_select_mysql)
        # 事务提交，否则数据库得不到更新
        conn.commit()
        if effect_row == 1:
            pass
        else:
            try:
                sql_insert = """INSERT INTO update_time_violation_info(时间, 名称, 微信号, 原始ID, 粉丝来源, 类型, 性别, 累计关注,
                违规类型, 违规内容, 违规处理, 是否封禁, 主体公司)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',
                '{9}','{10}','{11}','{12}')""".format(
                    result["时间"],  result["名称"], result["微信号"], result["原始ID"], result["粉丝来源"],
                    result["类型"], result["性别"], result["累计关注"], result["违规类型"], result["违规内容"], result["违规处理"],
                    result["是否封禁"], result["主体公司"])
                # 执行语句
                cursor.execute(sql_insert)
                # 事务提交，否则数据库得不到更新
                conn.commit()
                i += 1
                print("违规表格 数据入mysql库 {0}条".format(i))
            except:
                # Rollback in case there is any error
                conn.rollback()
    cursor.close()
    conn.close()


# 违规表格 写入json文件
def Violation_data_table(data_date, account, Type_of_violation, Irregularities, Violation_treatment,
                         Whether_or_not_to_ban, Accumulative_attention):
    info = {
        "时间": data_date,
        "名称": account['nickname'],
        "微信号": account['wx_id'],
        "原始ID": account['gh_id'],
        "粉丝来源": account['fans_src'],
        "类型": account['type'],
        "性别": account['gender'],
        "累计关注": Accumulative_attention,
        "违规类型": Type_of_violation,
        "违规内容": Irregularities,
        "违规处理": Violation_treatment,
        "是否封禁": Whether_or_not_to_ban,
        "主体公司": account['gs']
    }
    with codecs.open('Violation_data_table.json','a','utf-8') as f:
        f.write(json.dumps(info)+"\r")

def get_violation_info(item):
    title = item['Title']
    violation_content = re.sub('<.*?>', '', item['Content'])
    violation_cause = ''
    violation_title = ''
    violation_result = ''
    if re.search('违规处理', title):
        if re.search('涉[嫌及](.+?)，',violation_content):
            violation_cause = re.search('涉[嫌及](.+?)，', violation_content).group(1)
            violation_title = re.search(
                '违规(.*?)消息“(.*?)”', violation_content).group(2)
        else:
            violation_cause = re.search('发现此公众号(.+?)，', violation_content).group(1)
            violation_title = re.search(
                '违规(.*?)消息“(.*?)”', violation_content).group(2)
    elif re.search('功能屏蔽', title):
        if re.search('涉[嫌及](.+?)，', violation_content):
            violation_cause = re.search(
                '涉[嫌及](.+?)，', violation_content).group(1)
        elif re.search('您的帐号经查(.+?)，', violation_content):
            violation_cause = re.search(
                '您的帐号经查(.+?)，', violation_content).group(1)
        if re.search('违规内容：(.+?)如有异议', violation_content):
            violation_title = re.sub('&nbsp;', '', re.search(
                '违规内容：(.+?)如有异议', violation_content).group(1))
        elif re.search('流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content):
            violation_title = re.search(
                '流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content).group(0)
        features_by_date = {}
        features = re.findall('已屏蔽([^功能]+?)功能至([^,，]+)[，,]', violation_content)
        for feature in features:
            feature_name = feature[0]
            feature_date = feature[1]
            if feature_date in features_by_date:
                features_by_date[feature_date].append(feature_name)
            else:
                features_by_date[feature_date] = [feature_name]
        for feature_date, forbidden_features in features_by_date.items():
            violation_result += "{}: {}\n".format(
                feature_date, ','.join(forbidden_features))
    elif re.search('粉丝删除', title):
        violation_cause = re.search('所禁止的(.+?)，', violation_content).group(1)
        violation_title = re.sub('&nbsp;', '', re.search(
            '发起的"(.*?)"', violation_content).group(1))
        violation_result = re.search('已删除.+?粉丝数。', violation_content).group(0)
    elif re.search('责令处理', title):
        violation_title = re.search('图文消息“(.*?)”', violation_content).group(1)
        violation_result = re.search(
            '已被互联网信息内容主管部门责令删除', violation_content).group(0)
    return violation_cause, violation_title, violation_result


def get_violation_list(data_date, gh_id):
    return utils.get_json_file(gh_id, data_date, 'violation-list.json', data_type=list)